package cn.gxa.edu_adm.dao.impl;

import cn.gxa.edu_adm.bean.CourseType;
import cn.gxa.edu_adm.dao.CourseTypeDao;
import cn.gxa.edu_adm.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CourseTypeDaoImpl  implements CourseTypeDao {
    @Override
    public List<CourseType> findByManyWhere(Integer page, Integer limit, String name, Integer state) {
        //参数集合
        List<Object>  params=new ArrayList<>();
        //String 面试题 怎么优化sql  1.不能写1=1 2.尽量查询不用 *写上明确的查询字段
        StringBuffer  sql=new StringBuffer("select *  from CourseType  where 1=1");  //sql动态 拼接
        //name  有值 where  name=?
        if(name!=null){
            sql.append(" and  couTypeName like ?");
            params.add("%"+name+"%");
        }
        // courseType  where  courseType=?
        if(state!=null){
            sql.append(" and  couTypeState=?");
            params.add(state);
        }
        if(page!=null && limit!=null){
            //(page-1)*limit
            sql.append(" limit "+(page-1)*limit+","+limit);
        }
        System.out.println("sql:"+sql);
        //查询数据库
        QueryRunner   queryRunner  =new QueryRunner(DruidUtils.getDruidDataSource());
        try {
            return   queryRunner.query(sql.toString(),new BeanListHandler<>(CourseType.class),params.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<CourseType> findByManyWhere(String name, Integer state) {
        return findByManyWhere(null,null,name,state);
    }

    @Override
    public Long findByManyWhereCount(String name, Integer state) {
        //参数集合
        List<Object>  params=new ArrayList<>();
        //String 面试题 怎么优化sql  1.不能写1=1 2.尽量查询不用 *写上明确的查询字段
        StringBuffer  sql=new StringBuffer("select count(couTypeId)  from CourseType  where 1=1");  //sql动态 拼接
        //name  有值 where  name=?
        if(name!=null){
            sql.append(" and  couTypeName like ?");
            params.add("%"+name+"%");
        }
        // courseType  where  courseType=?
        if(state!=null){
            sql.append(" and  couTypeState=?");
            params.add(state);
        }
        //查询数据库
        QueryRunner   queryRunner  =new QueryRunner(DruidUtils.getDruidDataSource());
        try {
            Object  count=queryRunner.query(sql.toString(),new ArrayHandler(),params.toArray())[0];
            return   Long.parseLong(count.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
